package com.zlt.healthpass.dao;

import com.zlt.healthpass.bean.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @program: HealthPass
 * @description: 用户信息DAO
 * @author: Mr.abel(ShiJiaYi)
 * @create: 2022-07-13 14:26
 **/
@Mapper
public interface IUserInfoDao {

    @Select("SELECT id,uname,id_card FROM users WHERE nickname=#{nickname}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "uname",property = "name"),
            @Result(column = "id_card",property = "idCard")
    })
    UserInfo selectUserInfoByNickname(String nickname);


    @Select("SELECT health_status FROM users WHERE nickname=#{nickname}")
    int selectHealthStatusByNickname(String nickname);

    @Insert("INSERT INTO location_code(user_id,location_name,create_time) " +
            "VALUE(#{userId},#{localtionName},#{date})")
    int insertLocaltionCode(Localtion localtion);

    //用于核酸检测结果查询
    @Select("SELECT sampling_time,test_time,testing_hospital,result " +
            "FROM nucleic_acid_test " +
            "WHERE user_id = #{userId} " +
            "ORDER BY create_time DESC " +
            "LIMIT 1")
    @Results({
            @Result(column = "sampling_time",property = "samplingTime"),
            @Result(column = "test_time",property = "testTime"),
            @Result(column = "testing_hospital",property = "testingHospital"),
            @Result(column = "result",property = "result")
    })
    NucleicAcidTest selectTestByUserId(int userId);


    @Insert("INSERT INTO nucleic_acid_test(user_id,sampling_time,test_time,testing_hospital,result,create_time) " +
            "VALUE " +
            "(#{userId},#{samplingTime},#{testTime},#{testingHospital},#{result},#{createTime})")
    int uninfected(NucleicAcidTestSub nats);

    @Select("SELECT location_name,create_time FROM location_code " +
            "WHERE " +
            "user_id = #{userId} " +
            "AND " +
            "DATEDIFF(#{testTime},create_time) <= 14")
    @Results({
            @Result(column = "location_name",property = "locationName"),
            @Result(column = "create_time",property = "createTime")
    })
    List<InfectedLocation> getInfectedLocation(NucleicAcidTestSub nats);


    @Update("UPDATE users SET health_status = 2 " +
            "WHERE  " +
            "id IN ( " +
            "SELECT user_id " +
            "FROM location_code " +
            "WHERE location_name=#{locationName} " +
            "AND " +
            "(SELECT TIMESTAMPDIFF(MINUTE,DATE_FORMAT(#{createTime},'%Y-%m-%d %H:%i'),DATE_FORMAT(create_time,'%Y-%m-%d %H:%i')) FROM DUAL) > -50 " +
            "OR " +
            "(SELECT TIMESTAMPDIFF(MINUTE,DATE_FORMAT(#{createTime},'%Y-%m-%d %H:%i'),DATE_FORMAT(create_time,'%Y-%m-%d %H:%i')) FROM DUAL) < 240)")
    int updateUserHealthStatus(InfectedLocation il);


    @Update("UPDATE users SET health_status = 3 " +
            "WHERE  " +
            "id IN ( " +
            "SELECT user_id " +
            "FROM location_code " +
            "WHERE location_name=#{locationName} " +
            "AND " +
            "(SELECT TIMESTAMPDIFF(MINUTE,DATE_FORMAT(#{createTime},'%Y-%m-%d %H:%i'),DATE_FORMAT(create_time,'%Y-%m-%d %H:%i')) FROM DUAL) > -5 " +
            "OR " +
            "(SELECT TIMESTAMPDIFF(MINUTE,DATE_FORMAT(#{createTime},'%Y-%m-%d %H:%i'),DATE_FORMAT(create_time,'%Y-%m-%d %H:%i')) FROM DUAL) < 120)")
    int updateUserHealthStatusRed(InfectedLocation il);

    //提交用户位置信息
    @Insert("INSERT INTO location_msg(user_id,longitude,latitude,create_time) VALUE (#{userId},#{longitude},#{latitude},#{createTime})")
    int insertUserLocationMsg(LocationMsg lm);


}
